package com.cc.controller;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.util.Arrays;
import java.util.List;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;
@Controller
@RequestMapping("/excel")
public class RepeatController {
    private final static String excel2003L =".xls";    //2003- 版本的excel  
    private final static String excel2007U =".xlsx";   //2007+ 版本的excel  
    @RequestMapping("/download")
    public void downloadcontr(HttpServletResponse response) throws IOException {
        // 1.调用Service获取用户组成的分析集合，这里用模拟数据
        List<Person> list = Arrays.asList(new Person("码云", 100 * 100), 
                new Person("马化腾", 1000 * 100));
        // 2.excel文件对象
        XSSFWorkbook wb = new XSSFWorkbook();
        XSSFCellStyle cellStyle = wb.createCellStyle();
        
        cellStyle.setAlignment(HorizontalAlignment.CENTER); // 居中
        // 3.根据excel文件创建一个sheet对象
        Sheet sheet = wb.createSheet("users");// 创建一张表
        // 4.为sheet添加第1行t数据
        Row topRow = sheet.createRow(0);// 创建第一行，起始为0
        Cell cell1 = topRow.createCell(0);
        cell1.setCellStyle(cellStyle);
        cell1.setCellValue("序号");
         //合并单元格
        CellRangeAddress region = new CellRangeAddress(0, 0, 0, 5);
        sheet.addMergedRegion(region);
        //sheet.setHorizontallyCenter(true);
        Row titleRow = sheet.createRow(1);// 创建第一行，起始为0
        titleRow.createCell(0).setCellValue("序号");
        titleRow.createCell(1).setCellValue("姓名");// 第一列
        titleRow.createCell(2).setCellValue("公司");
        titleRow.createCell(3).setCellValue("添加人");
        titleRow.createCell(4).setCellValue("邮箱");
        titleRow.createCell(5).setCellValue("电话");
        // 5.为sheet添加后续行
        int cell = 2;
        for (Person riskUser : list) {
            Row row = sheet.createRow(cell);// 从第二行开始保存数据
            row.createCell(0).setCellValue(cell);
            row.createCell(1).setCellValue(riskUser.getName());// 将数据库的数据遍历出来
            row.createCell(2).setCellValue(riskUser.getCount());
            cell++;
        }
        // 6.设置Excel文件的名字
        String fileName = "user用户信息报表.xlsx";
        OutputStream outputStream = null;
        try {
            // 7.下载的文件的名字的编码为utf-8
            fileName = URLEncoder.encode(fileName, "UTF-8");
            // 设置ContentType请求信息格式 响应的内容格式
            // 8.输出数据类型--mima类型：参考：https://www.w3school.com.cn/media/media_mimeref.asp
            response.setContentType("application/vnd.ms-excel");
            // 9.控制用户请求所得的内容存为一个文件的时候提供一个默认的文件名，文件直接在浏览器上显示或者在访问时弹出文件下载对话框。
            // 参考：https://www.cnblogs.com/wq-9/articles/12165056.html
            response.setHeader("Content-disposition", "attachment;filename=" + fileName);
            // 10、通过response输出流输出excel对象
            outputStream = response.getOutputStream();
            wb.write(outputStream);
            outputStream.flush();
            outputStream.close();
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    public class Person {
        private String name;
        private int count;
        public Person(String name, int count) {
            this.name = name;
            this.count = count;
        }
        public String getName() {
            return name;
        }
        public void setName(String name) {
            this.name = name;
        }
        public int getCount() {
            return count;
        }
        public void setCount(int count) {
            this.count = count;
        }
    }
    @RequestMapping("/upload")
    @ResponseBody
    public String upload(MultipartFile multipartFile) throws IOException {
        //1.获取导入execl文件io流
        InputStream is = multipartFile.getInputStream();
        try{
           //2.根据io流构建 execl文档 对象
           Workbook workBook = this.getWorkbook(is, multipartFile.getOriginalFilename());
           //3.获取第1个 sheet
           Sheet sheet = workBook.getSheetAt(0);
            if (sheet != null){
                //获取sheet中非常规的行数
                for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++){
                    //4.获取电子表格的每一行
                    Row row = sheet.getRow(i);
                    //获取每一行非常规的列数
                    for (int j = 0; j < row.getPhysicalNumberOfCells(); j++){
                        //5.获取每一列
                        Cell cell = row.getCell(j);
                        //6.获取每个单元格的值
                        String cellStr = cell.toString();
                        System.out.print("【"+cellStr+"】 ");
                    }
                    System.out.println();
                }
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        return "success";
    }
    /** 
     * 描述：根据文件后缀，自适应上传文件的版本  
     * @param inStr,fileName 
     * @return 
     * @throws Exception 
     */  
    public  Workbook getWorkbook(InputStream inStr,String fileName) throws Exception{  
        Workbook wb = null;  
        String fileType = fileName.substring(fileName.lastIndexOf("."));  
        if(excel2003L.equals(fileType)){  
            wb = new HSSFWorkbook(inStr);  //2003-  
        }else if(excel2007U.equals(fileType)){  
            wb = new XSSFWorkbook(inStr);  //2007+  
        }else{  
            throw new Exception("解析的文件格式有误！");  
        }  
        return wb;  
    }  
  
}





